﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using OffInvoiceExcelTemplate;
using Microsoft.Office.Interop.Excel;

namespace OffInvoiceExcelTemplate
{
    public partial class Hoja2
    {
        Hoja1 h1;
        private void Hoja2_Startup(object sender, System.EventArgs e)
        {
            h1 = Globals.Hoja1;
            //loadComboBoxes();
        }

        private void Hoja2_Shutdown(object sender, System.EventArgs e)
        {
           
        }

        public void edit(Excel.Workbook wb, bool protect)
        {
            Excel.Worksheet ws =(Excel.Worksheet) wb.Sheets[1];
            System.Resources.ResourceManager rm = new System.Resources.ResourceManager(typeof(OffInvoiceExcelTemplate.Properties.Resources));
            string ctrs = rm.GetString("p_crsn");

            byte[] decbuff = Convert.FromBase64String(ctrs);
            ctrs = System.Text.Encoding.UTF8.GetString(decbuff);

            if (protect)
            {
                ws.Protect(ctrs, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                wb.Protect(ctrs, missing, missing);
            }
            else
            {
                ws.Unprotect(ctrs);
                wb.Unprotect(ctrs);
            }
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.hidecontrols.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.hidecontrols_Change);
            this.Startup += new System.EventHandler(this.Hoja2_Startup);
            this.Shutdown += new System.EventHandler(this.Hoja2_Shutdown);

        }

        #endregion

     


        /// <summary>
        /// carga los datos de un archivo de texto delimitado por tabulacion, a una hoja de excel
        /// </summary>
        /// <param name="filePath">path del archivo de datos</param>
        /// <param name="fileName">nombre sin extension del archivo de datos</param>
        /// <param name="rangeName">nombre del rango en donde se insertaran los datos</param>
        /// <param name="columnDataTypes">array que indica el tipo de datos para cada columna, normalmente con el valor de 1 para c/columna</param>
        private void importTxt(string filePath, string fileName, string rangeName, object[] columnDataTypes)
        {
            try
            {

                Excel.Worksheet ws = this.Application.Sheets[2];                        
                Excel.Range rng = ws.get_Range(rangeName, missing);
                int nqueries = ws.QueryTables.Count;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing);
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).Name = "";
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).FieldNames = true;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).RowNumbers = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).FillAdjacentFormulas = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).PreserveFormatting = true;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).RefreshOnFileOpen = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).SavePassword = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).SaveData = true;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).AdjustColumnWidth = true;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).RefreshPeriod = 0;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFilePromptOnRefresh = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFilePlatform = 1252;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileStartRow = 1;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileParseType = XlTextParsingType.xlDelimited;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileConsecutiveDelimiter = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileTabDelimiter = true;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileSemicolonDelimiter = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileCommaDelimiter = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileSpaceDelimiter = false;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileColumnDataTypes = columnDataTypes;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).TextFileTrailingMinusNumbers = true;
                ws.QueryTables.Add(@"TEXT;" + filePath, rng, missing).Refresh(false);



            }
            catch (Exception) { }
        }

        private void loadComboBoxes()
        {
            string filePath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\Microsoft OffInvoice\InformationFiles\" + h1.culture.Name;

            object[] columnDataTypes = { 2, 2 };

            try
            {
                Excel.Workbook wb = this.Application.ActiveWorkbook;
                Excel.Worksheet ws = wb.Sheets[2];
                ws.Rows.ClearContents();
              
            }
            catch (Exception) { }

            ////importar tipos de factura
            importTxt(string.Concat(filePath, @"\invoiceType.txt"), "invoiceType", "InvoiceTypeNandV", columnDataTypes);

            ////importar tipos de emisor
            importTxt(string.Concat(filePath, @"\issuerType.txt"), "issuerType", "InvoiceIssuerNAndV", columnDataTypes);

            ////importar listado de paises
            importTxt(string.Concat(filePath, @"\Countries.txt"), "Countries", "countrycodeslist", columnDataTypes);

            //importar tipo de persona
            importTxt(string.Concat(filePath, @"\PersonType.txt"), "PersonType", "PersonTypeNandV", columnDataTypes);

            //importar tipo de residente
            importTxt(string.Concat(filePath, @"\ResidenceType.txt"), "ResidenceType", "ResidentTypeNandV", columnDataTypes);

            //importar motivo de rectificacion
            importTxt(string.Concat(filePath, @"\correctionReason.txt"), "correctionReason", "CORR_ReasonDescriptionNandV", columnDataTypes);

            //importar metodo de rectificacion
            importTxt(string.Concat(filePath, @"\correctionMethod.txt"), "correctionMethod", "CORR_CorrectionMethodDescriptionNAndV", columnDataTypes);

            //importar codigos de monedas
            object[] columnDataTypes2 = { 2 };

            importTxt(string.Concat(filePath, @"\currency.txt"), "currency", "currencycodes", columnDataTypes2);



        }

        private void hidecontrols_Change(Range Target)
        {
            try
            {
                if (hidecontrols.Value2.ToString().ToLower().Equals("true"))
                {
                    h1.dtp_c_taxped.Visible = false;
                    h1.dtp_c_taxpsd.Visible = false;
                    //h1.dateTimePicker2.Visible = true;

                }
                else
                {
                    h1.dtp_c_taxped.Visible = true;
                    h1.dtp_c_taxpsd.Visible = true;
                    //h1.dateTimePicker2.Visible = false;
                }

            }
            catch (Exception) { }
        }


    }
}
